﻿using OfficeOpenXml;
using supermarket.method;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace supermarket
{
    public partial class employeeinformation : Form
    {
        Classadmin classadmin = new Classadmin();
        public SqlConnection sql = new Classgetsql().getcon();
        public employeeinformation ()
        {
            InitializeComponent();
        }

        private void employeeinformation_Load ( object sender, EventArgs e )
        {
            LoadData(dgv_admin, "employee");
            LoadShopComboBox();
            dgv_admin.DoubleClick += dgv_admin_CellDoubleClick;
        }

        #region 加载信息

        public void LoadData ( DataGridView dgv, string tableType )
        {
            try
            {

                string query;
                Classgetsql sqlcon = new Classgetsql();
                SqlConnection con = sqlcon.getcon();  //数据库已连接并打开
                SqlCommand cmd;
                SqlDataReader qlddr = null;  //数据缓冲区

                query = "SELECT TOP 1000 id, [username],[Role],[Tip],[passwd],phone FROM [super].[dbo].[SystemAccounts]";
                cmd = new SqlCommand(query, con);   //执行SQL语句
                qlddr = cmd.ExecuteReader();   //把sql语句执行结果读取到数据缓冲区qlddr中

                // Clear existing data
                dgv.Rows.Clear();
                int i = 0;
                while (qlddr.Read())
                {
                    dgv.Rows.Add();
                    dgv[0, i].Value = qlddr[0].ToString();  // id
                    dgv[1, i].Value = qlddr[1].ToString();  // username
                    dgv[2, i].Value = qlddr[2].ToString();  // passwd
                    dgv[3, i].Value = qlddr[3].ToString();  // Role
                    dgv[4, i].Value = qlddr[4].ToString();  // Tip
                    dgv[5, i].Value = qlddr[5].ToString();  // phone
                    i++;
                }
                qlddr.Close();


                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载数据时发生错误: " + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void LoadShopComboBox ()
        {
            try
            {
                string query = "SELECT shop_name FROM [super].[dbo].[shop_List]";
                using (SqlCommand cmd = new SqlCommand(query, sql))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        cboTip.Items.Clear();
                        cboTip.Items.Add("");
                        while (reader.Read())
                        {
                            cboTip.Items.Add(reader["shop_name"].ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"加载门店列表时发生错误：{ex.Message}");
            }
        }
        #endregion

        private void button10_Click ( object sender, EventArgs e )
        {
            string username = txtUsername1.Text.Trim();
            string password = textBox5.Text.Trim();
            string role = cmbRole.SelectedItem?.ToString();
            string phone = tb_phone.Text.Trim();

            if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(password) || string.IsNullOrEmpty(role))
            {
                MessageBox.Show("用户名、密码和角色不能为空！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (!string.IsNullOrEmpty(phone) && !CommonHelper.IsValidPhoneNumber(phone))
            {
                MessageBox.Show("请输入有效的11位手机号！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            AddUser(username, password, role, phone);
        }

        private void button9_Click ( object sender, EventArgs e )
        {
            string username = txtUsername1.Text.Trim();
            string role = cmbRole.SelectedItem?.ToString();
            string phone = tb_phone.Text.Trim();
            QueryUsers("", username, role, phone);
        }

        private void button7_Click ( object sender, EventArgs e )
        {
            if (string.IsNullOrEmpty(txtUsername1.Text))
            {
                MessageBox.Show("请先选择要修改的员工！");
                return;
            }

            string username = txtUsername1.Text.Trim();
            string password = textBox5.Text.Trim();
            string role = cmbRole.SelectedItem?.ToString();
            string phone = tb_phone.Text.Trim();
            string id = txtid.Text.Trim();

            if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(password) || string.IsNullOrEmpty(role))
            {
                MessageBox.Show("用户名、密码和角色不能为空！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (!string.IsNullOrEmpty(phone) && !System.Text.RegularExpressions.Regex.IsMatch(phone, @"^\d{11}$"))
            {
                MessageBox.Show("请输入有效的11位手机号！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            UpdateUser(id, username, password, role, phone);
        }

        private void button8_Click ( object sender, EventArgs e )
        {
            if (string.IsNullOrEmpty(txtid.Text))
            {
                MessageBox.Show("请先选择要删除的员工！");
                return;
            }

            string id = txtid.Text.Trim();
            DeleteUser(id);
        }

        private void dgv_admin_CellDoubleClick ( object sender, EventArgs e )
        {
            DataGridViewRow row = dgv_admin.CurrentRow;
            if (row != null)
            {
                txtid.Text = row.Cells["id"].Value.ToString();
                txtUsername1.Text = row.Cells["username"].Value.ToString();
                cmbRole.Text = row.Cells["Role"].Value.ToString();
                cboTip.Text = row.Cells["Tip"].Value?.ToString() ?? "";
                tb_phone.Text = row.Cells["phone"].Value?.ToString() ?? "";
                textBox5.Text = row.Cells["passwd"].Value?.ToString() ?? "";
            }
        }

        private void AddUser ( string username, string password, string role, string phone )
        {
            try
            {
                // 检查用户名是否已存在
                string checkQuery = string.Format("SELECT COUNT(*) FROM SystemAccounts WHERE username = '{0}'", username);
                using (SqlCommand checkCommand = new SqlCommand(checkQuery, sql))
                {
                    int count = (int)checkCommand.ExecuteScalar();
                    if (count > 0)
                    {
                        MessageBox.Show("该username已存在！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }

                // 插入新用户
                string insertQuery = string.Format("INSERT INTO SystemAccounts (username, passwd, Role, Tip, phone) VALUES ('{0}','{1}','{2}','{3}','{4}')", username, password, role, cboTip.SelectedItem?.ToString(), phone);
                using (SqlCommand insertCommand = new SqlCommand(insertQuery, sql))
                {
                    int rowsAffected = insertCommand.ExecuteNonQuery();
                    MessageBox.Show(rowsAffected > 0 ? "用户添加成功！" : "用户添加失败。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    ClearEmployeeInputFields();
                    // 刷新 DataGridView
                    QueryUsers("", "", null, "");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"添加用户时发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void QueryUsers ( string id, string username, string role, string phone )
        {
            try
            {
                StringBuilder query = new StringBuilder("SELECT id, [username],[Role],[Tip],[passwd],phone FROM SystemAccounts WHERE 1=1");
                if (!string.IsNullOrEmpty(id))
                    query.AppendFormat(" AND id = '{0}'", id);
                if (!string.IsNullOrEmpty(username))
                    query.AppendFormat(" AND username LIKE '%{0}%'", username);
                if (!string.IsNullOrEmpty(role))
                    query.AppendFormat(" AND Role = '{0}'", role);
                if (!string.IsNullOrEmpty(phone))
                    query.AppendFormat(" AND phone LIKE '%{0}%'", phone);

                Classgetsql sqlcon = new Classgetsql();
                SqlConnection con = sqlcon.getcon();  //数据库已连接并打开
                SqlCommand cmd = new SqlCommand(query.ToString(), con);
                SqlDataReader qlddr = cmd.ExecuteReader();

                // Clear existing data
                dgv_admin.Rows.Clear();
                int i = 0;
                while (qlddr.Read())
                {
                    dgv_admin.Rows.Add();
                    dgv_admin[0, i].Value = qlddr[0].ToString();  // id
                    dgv_admin[1, i].Value = qlddr[1].ToString();  // username
                    dgv_admin[2, i].Value = qlddr[2].ToString();  // passwd
                    dgv_admin[3, i].Value = qlddr[3].ToString();  // Role
                    dgv_admin[4, i].Value = qlddr[4].ToString();  // Tip
                    dgv_admin[5, i].Value = qlddr[5].ToString();  // phone
                    i++;
                }
                qlddr.Close();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show($"查询用户时发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void UpdateUser ( string id, string username, string password, string role, string phone )
        {
            try
            {
                // 检查用户名是否被其他用户使用
                string checkQuery = string.Format("SELECT COUNT(*) FROM SystemAccounts WHERE username = '{0}' AND id != '{1}'", username, id);
                using (SqlCommand checkCommand = new SqlCommand(checkQuery, sql))
                {
                    int count = (int)checkCommand.ExecuteScalar();
                    if (count > 0)
                    {
                        MessageBox.Show("该用户名已被其他用户使用！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }

                // 更新用户
                string updateQuery = string.Format("UPDATE SystemAccounts SET username = '{0}', passwd = '{1}', Role = '{2}', Tip = '{3}', phone = '{4}' WHERE id = {5}", username, password, role, cboTip.SelectedItem?.ToString(), phone, id);
                using (SqlCommand updateCommand = new SqlCommand(updateQuery, sql))
                {
                    int rowsAffected = updateCommand.ExecuteNonQuery();
                    MessageBox.Show(rowsAffected > 0 ? "用户修改成功！" : "用户修改失败。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    ClearEmployeeInputFields();
                    // 刷新 DataGridView
                    QueryUsers("", "", null, "");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"修改用户时发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void DeleteUser ( string id )
        {
            try
            {
                DialogResult result = MessageBox.Show("确定要删除该员工吗？", "确认删除",
                    MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (result == DialogResult.Yes)
                {
                    string deleteQuery = string.Format("DELETE FROM SystemAccounts WHERE id = '{0}'", id);
                    using (SqlCommand deleteCommand = new SqlCommand(deleteQuery, sql))
                    {
                        int rowsAffected = deleteCommand.ExecuteNonQuery();
                        MessageBox.Show(rowsAffected > 0 ? "员工删除成功！" : "员工删除失败。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        ClearEmployeeInputFields();
                        // 刷新 DataGridView
                        QueryUsers("", "", null, "");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"删除用户时发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void ClearEmployeeInputFields ()
        {
            txtid.Text = "";
            txtUsername1.Text = "";
            textBox5.Text = "";
            cmbRole.Text = "";
            tb_phone.Text = "";
            cboTip.Text = "";
        }
    }
}
